
/*********************************************************
 *
 *  Project:      GrabChineseLessons
 *
 *  Class:        HZAddAccessor
 *
 *  File name:    HZAddAccessor.java
 *
 *  Parent class:
 *
 *********************************************************/

package com.eastmountain.chinese.db.flashcards.access;

import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.GregorianCalendar;
import java.util.logging.Handler;
import java.util.logging.Level;

import javax.swing.JOptionPane;

import com.eastmountain.chinese.db.exceptions.DBAccessException;
import com.eastmountain.chinese.db.flashcards.connection.DBAccessData;
import com.eastmountain.chinese.db.flashcards.records.ChineseCharacterLessonXRF;
import com.eastmountain.chinese.db.flashcards.records.ChineseCharacterProperties;
import com.eastmountain.chinese.db.flashcards.records.ChineseElementProperties;
import com.eastmountain.chinese.db.flashcards.records.ChineseWordLessonXRF;
import com.eastmountain.chinese.db.flashcards.records.ChineseWordProperties;
import com.eastmountain.chinese.db.flashcards.records.Lesson;

public class HZAddAccessor extends HZAccessor {

    private ResultSet resultSet = null;

    private PreparedStatement lessonInsertStmt;
    private static final String lessonInsertSQL = "INSERT into LESSONS "
        + "  ( lessonNumber, level, title, "
        + "simplifiedTraditional, "
        + "source, creationDate )"
        + "VALUES ( ?, ?, ?, ?, ?, ? )";

    private PreparedStatement wordInsertStmt;
    private static final String wordInsertSQL
        = "INSERT into WORDS "
            + "( word, simplifiedTraditional, "
            + "lessonNumber, level, "
            + "lessonSection, "
            + "pronunciation, definition, "
            + " fileName, sound ) "
        + " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

    private PreparedStatement characterInsertStmt;
    private static final String characterInsertSQL
        = "INSERT into CHARACTERS "
            + "( hanZi, simplifiedTraditional, "
            + "lessonNumber, level, "
            + "lessonSection, "
            + "pronunciation, definition, "
            + "fileName, sound, "
            + "kangXiRadicalNumber, simplifiedRadical, additionalStrokes )"
        + " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

    //  TODO remove orderInLesson
    
    private PreparedStatement characterLessonXRFInsertStmt;
    private static final String characterLessonXRFInsertSQL = "INSERT into CHARACTERLESSONXRF "
        + "  ( hanZi, simplifiedTraditional, lessonNumber )"
        + " VALUES ( ?, ?, ? )";

    private PreparedStatement wordLessonXRFInsertStmt;
    private static final String wordLessonXRFInsertSQL = "INSERT into WORDLESSONXRF"
        + " ( word, simplifiedTraditional, lessonNumber, orderInLesson )"
        + " VALUES ( ?, ?, ?, ? )";
    
    private int lessonRecordsInserted             = 0;
    private int characterRecordsInserted          = 0;
    private int characterLessonXRFRecordsInserted = 0;
    private int wordRecordsInserted               = 0;
    private int wordLessonXRFRecordsInserted      = 0;


    /*************************************************
     *
     *   CLASS - HZAddAccessor
     *
     ************************************************/

    public HZAddAccessor( DBAccessData dbURLs ) throws DBAccessException {
        
        //this.dbURLs = dbURLs;

        super( dbURLs );

    }

    /*************************************************
     *
     *   addLesson
     *
     ************************************************/

    public int addLesson( Lesson lesson ) throws DBAccessException {   

        int result = -1;

        String sqlState = "";
        int errorCode = -1;

        GregorianCalendar gregorianCalendar = new GregorianCalendar();

        logger.info("****** HZAddAccessor.addLesson() called with: "
        + lesson.getLessonNumber() + ", " + lesson.getCreationDate() + " " );

        try {

            lessonInsertStmt.setShort( 1, lesson.getLessonNumber() );
            lessonInsertStmt.setString( 2, lesson.getLevel() );
            lessonInsertStmt.setString( 3, lesson.getTitle() );
            lessonInsertStmt.setString( 4, lesson.getSimplifiedTraditionalLesson() );
            lessonInsertStmt.setString( 5, lesson.getSource() );

            java.sql.Timestamp sqlTimestamp
                = new java.sql.Timestamp( gregorianCalendar.getTime().getTime() );
            lessonInsertStmt.setTimestamp( 6, sqlTimestamp );

            result = lessonInsertStmt.executeUpdate();
            
            lessonRecordsInserted++;

            // logger.info( "*** INSERT Stmt executed *** result: " + result );

        } catch ( SQLException sqlException ) {

            // TODO The SQL Exception will be handled in an aspect

            sqlState = ((SQLException) sqlException).getSQLState();
            errorCode = ((SQLException) sqlException).getErrorCode();
            
            //  XJ015 - Derby system shutdown.

            if ( "XJ015".equals( sqlState ) ) {

                logger.log( Level.INFO, "\nSQLException: " + sqlException.getMessage() + "\n"
                                                + "SQLState: " + sqlState + "\n"
                                                + "VendorError: "
                                                + errorCode );

                //reportExceptions = false;
                
            //  23505 - The statement was aborted because it would have
            //          caused a duplicate key value in a unique
            //          or primary key constraint or unique index 
            //          identified by '<value>' defined on '<value>'.

            } else if ( !"23505".equals( sqlState ) ) {

                logger.log( Level.SEVERE, "SQLException: " + sqlException.getMessage() + "\n"
                                                + "SQLState: " + sqlState + "\n"
                                                + "VendorError: "
                                                + errorCode );

                JOptionPane.showMessageDialog( null, sqlException.getMessage(), "DB Error",
                                                JOptionPane.ERROR_MESSAGE );

                throw new DBAccessException( "SQL Exception" );

            } else {

                // TODO warn of duplicate

                logger.log( Level.WARNING, "SQLException: "
                                                + "SQLState: " + sqlState
                                                + " - VendorError: "
                                                + errorCode );

            }



        } catch ( Exception exception ) {

            tableName = LESSONS;

            //reportException( exception, tableName, lesson );
            throw new DBAccessException( "Unexpected Exception", lesson, "LESSONS", exception );

        } finally {

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        return (result);

    }

    /*************************************************
     *
     *   addWord
     *
     ************************************************/

    public int addWord( ChineseWordProperties wordProperties ) throws DBAccessException {

        //logger.info("****** HZAddAccessor.addWord() called with: "
        //             + wordProperties.toString() );

        ByteArrayInputStream inputStream = null; 
        int result = -1;

        try {
            
            wordInsertStmt.setString( 1, wordProperties.getChineseElement() );
            wordInsertStmt.setString( 2, wordProperties.getSimplifiedTraditional() );
            wordInsertStmt.setShort(  3, wordProperties.getLessonNumber() );
            wordInsertStmt.setString( 4, wordProperties.getLevel() );
            wordInsertStmt.setString( 5, wordProperties.getLessonSection() );
            wordInsertStmt.setString( 6, wordProperties.getPinyin() );
            wordInsertStmt.setString( 7, wordProperties.getEnglish( ) );
            wordInsertStmt.setString( 8, wordProperties.getFileName() );
            
            inputStream = setSoundValue( wordProperties );
            if ( inputStream != null  ) {
                
                wordInsertStmt.setBinaryStream( 9, inputStream,
                                                inputStream.available() );    
                
            } else {
                
                wordInsertStmt.setBlob( 9, null );
                
            }

            //  TODO handle this by catching the truncation exception

            result = wordInsertStmt.executeUpdate();
            
            wordRecordsInserted++;

            logger.info( "*** Word INSERT Stmt executed *** result: " + result );

        } catch ( SQLException sqlException ) {

            // The SQL Exception will be handled in an aspect

        } catch ( Exception exception ) {

            tableName = WORDS;

            //reportException( exception, tableName, wordProperties );
            throw new DBAccessException( "Unknown Exception", wordProperties, "WORDS", exception );

        } finally {

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        return (result);

    }

    /*************************************************
     *
     *   addCharacter
     *
     ************************************************/

    public int addCharacter( ChineseCharacterProperties characterProperties ) throws DBAccessException {

        //logger.info("****** HZAddAccessor.addCharacter invoked " );

        ByteArrayInputStream inputStream = null; 
        
        //Blob soundBlob;
        //byte[] soundArray = null;

        int result = -1;

        String definition;

        try {

            characterInsertStmt.setString( 1, characterProperties.getChineseElement() );
            characterInsertStmt.setString( 2, characterProperties.getSimplifiedTraditional() );
            characterInsertStmt.setShort(  3, characterProperties.getLessonNumber() );     
            characterInsertStmt.setString( 4, characterProperties.getLevel() );
            characterInsertStmt.setString( 5, characterProperties.getLessonSection() );
            characterInsertStmt.setString( 6, characterProperties.getPinyin() );
            definition = truncate( characterProperties.getEnglish(), 200 );
            characterInsertStmt.setString( 7, definition );
            characterInsertStmt.setString( 8, characterProperties.getFileName() );
            
            inputStream = setSoundValue( characterProperties );
            if ( inputStream != null  ) {
                
                characterInsertStmt.setBinaryStream( 9, inputStream,
                                                inputStream.available() );    
                
            } else {
                
                characterInsertStmt.setBlob( 9, null );
                
            }
            
            characterInsertStmt.setShort(  10, characterProperties.getKangXiRadicalNumber() );
            characterInsertStmt.setShort(  11, characterProperties.getSimplifiedRadical() );
            characterInsertStmt.setShort(  12, characterProperties.getAdditionalStrokes() );

            result = characterInsertStmt.executeUpdate();
            
            characterRecordsInserted++;

            logger.info( "*** Character INSERT Stmt executed *** result: " + result );

        } catch ( SQLException sqlException ) {

            // The SQL Exception will be handled in an aspect

        } catch ( Exception exception ) {

            tableName = CHARACTERS;

            //reportException( exception, tableName, characterProperties );
            throw new DBAccessException( "Unknown Exception", characterProperties, "CHARACTERS", exception );

        } finally {

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        return (result);

    }

    /*************************************************
     *
     *   addWordLessonXRF
     *
     ************************************************/

    public int addWordLessonXRF( ChineseWordLessonXRF wordLessonXRF ) throws DBAccessException {

        //logger.info( "****** HZAddAccessor.addWordLessonXRF() called with: "
        //             + wordXRF.toString() );

        int result = -1;

        try {

            wordLessonXRFInsertStmt.setString( 1, wordLessonXRF.getChineseElement() );
            wordLessonXRFInsertStmt.setString( 2, wordLessonXRF.getSimplifiedTraditional() );
            wordLessonXRFInsertStmt.setShort(    3, wordLessonXRF.getLessonNumber() );
            wordLessonXRFInsertStmt.setShort(    4, wordLessonXRF.getOrderInLesson() );

            result = wordLessonXRFInsertStmt.executeUpdate();
            
            wordLessonXRFRecordsInserted++;

            logger.info( "*** WordXRF INSERT Stmt executed *** result: " + result );

        } catch ( SQLException sqlException ) {

            // The SQL Exception will be handled in an aspect

        } catch ( Exception exception ) {

            tableName = WORDXRF;

            //reportException( exception, tableName, wordLessonXRF );
            throw new DBAccessException( "Unknown Exception", wordLessonXRF, "WORDXRF", exception );

        } finally {

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        return (result);

    }

    /*************************************************
     *
     *   addCharacterLessonXRF
     *
     ************************************************/

    public int addCharacterLessonXRF( ChineseCharacterLessonXRF characterXRF ) throws DBAccessException {

        //logger.info( "\n******\n****** HZAddAccessor.addCharacterLessonXRF() called with: "
        //             + characterXRF.toString() + "\n******" );

        int result = -1;

        try {

            characterLessonXRFInsertStmt.setString( 1, characterXRF.getChineseElement() );
            characterLessonXRFInsertStmt.setString( 2, characterXRF.getSimplifiedTraditional() );
            characterLessonXRFInsertStmt.setShort(    3, characterXRF.getLessonNumber() );

            result = characterLessonXRFInsertStmt.executeUpdate();
            
            characterLessonXRFRecordsInserted++;

            logger.info( "*** CharacterXRF INSERT Stmt executed *** result: " + result );

        } catch ( SQLException sqlException ) {

            // The SQL Exception will be handled in an aspect

        } catch ( Exception exception ) {

            tableName = CHARACTERXRF;

            //reportException( exception, tableName, characterXRF );
            throw new DBAccessException( "Unknown Exception", characterXRF, "CHARACTERXRF", exception );

        } finally {

            for ( Handler h : logger.getHandlers() ) {
                h.flush();
            }

        }

        return (result);

    }

    private String truncate( String string, int length ) {

        String truncatedString = string;

        if ( string.length() > length ) {

            truncatedString = string.substring(  0, 100 );

        }

        return( truncatedString );

    }

    private ByteArrayInputStream setSoundValue( ChineseElementProperties elementProperties )
            throws SQLException {
        
        ByteArrayInputStream inputStream = null;
        
        if ( elementProperties.getSound() != null ) {
            
            inputStream = elementProperties.getSound();
            
        }
        
        return( inputStream );
        
     }

    /*************************************************
     *
     *   setupPreparedStatements
     *
     ************************************************/

    @Override
    protected void setupPreparedStatements() throws DBAccessException {

        logger.info( "****** HZAddAccessor.setupPreparedStatements() called" );

        try {

            logger.info( "===*** about to call flashCardDBConnection.prepareStatement()" );

            logger.info( "===*** Stmt:  " + lessonInsertSQL );
            lessonInsertStmt = flashCardDBConnection.prepareStatement( lessonInsertSQL );

            logger.info( "===*** Stmt:  " + wordInsertSQL );
            wordInsertStmt = flashCardDBConnection.prepareStatement( wordInsertSQL );

            logger.info( "===*** Stmt:  " + characterInsertSQL );
            characterInsertStmt = flashCardDBConnection.prepareStatement( characterInsertSQL );

            logger.info( "===*** Stmt:  " + characterLessonXRFInsertSQL );
            characterLessonXRFInsertStmt = flashCardDBConnection.prepareStatement( characterLessonXRFInsertSQL );

            logger.info( "===*** Stmt:  " + wordLessonXRFInsertSQL );
            wordLessonXRFInsertStmt = flashCardDBConnection.prepareStatement( wordLessonXRFInsertSQL );

            logger.info( "*** Statements Prepared" );

        } catch ( SQLException ex ) {

            logger.severe( "Select: SQLException: " + ex.getMessage() + "\n" + "SQLState: "
                            + ex.getSQLState() + "\n" + "VendorError: " + ex.getErrorCode() );

            throw new DBAccessException( "Error in creating Prepared Statement", ex );

        } catch ( Exception e ) {

            logger.severe( "*** Select: Unknown Exception:" + e.getClass().getName() + " MSG: "
                            + e.getMessage() );

        }
        

    }

    public int getLessonRecordsInserted() {
        return lessonRecordsInserted;
    }

    public void setLessonRecordsInserted( int lessonRecordsInserted ) {
        this.lessonRecordsInserted = lessonRecordsInserted;
    }

    public int getCharacterRecordsInserted() {
        return characterRecordsInserted;
    }

    public void setCharacterRecordsInserted( int characterRecordsInserted ) {
        this.characterRecordsInserted = characterRecordsInserted;
    }

    public int getCharacterLessonXRFRecordsInserted() {
        return characterLessonXRFRecordsInserted;
    }

    public void setCharacterLessonXRFRecordsInserted(
            int characterLessonXRFRecordsInserted ) {
        this.characterLessonXRFRecordsInserted = characterLessonXRFRecordsInserted;
    }

    public int getWordRecordsInserted() {
        return wordRecordsInserted;
    }

    public void setWordRecordsInserted( int wordRecordsInserted ) {
        this.wordRecordsInserted = wordRecordsInserted;
    }

    public int getWordLessonXRFRecordsInserted() {
        return wordLessonXRFRecordsInserted;
    }

    public void setWordLessonXRFRecordsInserted( int wordLessonXRFRecordsInserted ) {
        this.wordLessonXRFRecordsInserted = wordLessonXRFRecordsInserted;
    }
    
/*
    *//*************************************************
     *
     *   reportException
     *
     ************************************************//*

    private void reportException( Exception exception,
                                  String tableName,
                                  Object object ) throws DBAccessException {

        if ( exception instanceof SQLException ) {

            String sqlState = ((SQLException)exception).getSQLState();

            if ( ! "23505".equals( sqlState ) ) {

                logger.log( Level.SEVERE, "SQLException: " + exception.getMessage()
                                + "\n" + "SQLState: " + sqlState
                                + "\n" + "VendorError: " + ((SQLException)exception).getErrorCode()
                                + "\n" + object.toString()
                                + "\n in " + tableName
                                +"\n" );

                throw new DBAccessException( "SQL Exception" );

            } else {

                logger.log( Level.WARNING, object.toString()
                            + "\n already in " + tableName
                            + "\n" );

            }

        } else if ( exception instanceof Exception ) {

            logger.log( Level.SEVERE, "*** Unexpected Exception:" + exception.getClass().getName() + " MSG: "
                            + exception.getMessage(), exception
                            + "\n" + object.toString()
                            + "\n in " + tableName
                            +"\n" );


            exception.printStackTrace();

            logger.log( Level.SEVERE, "*** End of my Stack Trace" );

        }

    }
*/

}